﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using QuanLyDoanVien.General;
using System.Data;
using System.Data.SqlClient;
using QuanLyDoanVien.Bussiness;

namespace QuanLyDoanVien.DataLayer
{
    class HocSinhViPhamData
    {
        DataService m_HocSinhViPhamData = new DataService();
        public List<HocSinhViPhamInfo> layDanhSachHocSinhViPham()
        {

            DataService.OpenConnection();
            List<HocSinhViPhamInfo> rs = new List<HocSinhViPhamInfo>();
            string sql = string.Format("select * from Tab_HocSinhViPham");
            SqlCommand command = new SqlCommand(sql);
            m_HocSinhViPhamData.Load(command);
            foreach(DataRow dr in m_HocSinhViPhamData.Rows){
                HocSinhViPhamInfo hs = new HocSinhViPhamInfo();
                hs.MaKyLuat = dr["MaKyLuat"].ToString();
                hs.MaHocSinh = dr["MaHocSinh"].ToString();
                hs.NgayViPham = dr["NgayViPham"].ToString();
                hs.GhiChu = dr["GhiChu"].ToString();

                rs.Add(hs);
            }
            m_HocSinhViPhamData.CloseConnection();
            return rs;
        }

        public HocSinhViPhamInfo layHocSinhViPham(string maKyLuat)
        {
            DataService.OpenConnection();
            HocSinhViPhamInfo hs = null;
            string sql = string.Format("select * from Tab_HocSinhViPham where MaKyLuat='{0}'", maKyLuat);
            SqlCommand command = new SqlCommand(sql);
            m_HocSinhViPhamData.Load(command);
            if (m_HocSinhViPhamData.Rows.Count == 0)
            {
                m_HocSinhViPhamData.CloseConnection();
                return hs;
            }

            hs = new HocSinhViPhamInfo();
            DataRow dr = m_HocSinhViPhamData.Rows[0];
            hs.MaKyLuat = dr["MaKyLuat"].ToString();
            hs.MaHocSinh = dr["MaHocSinh"].ToString();
            hs.NgayViPham = dr["NgayViPham"].ToString();
            hs.GhiChu = dr["GhiChu"].ToString();
            m_HocSinhViPhamData.CloseConnection();
            return hs;
        }

        public DataTable layDSHSViPham()
        {

            DataService.OpenConnection();

            string sql = "select hs.MaHocSinh, hs.HoTen, l.TenLop, kl.TenKyLuat,kl.MaKyLuat, hsvp.NgayViPham, hs.AnhHocSinh, hsvp.GhiChu";
            sql += " from ((Tab_HocSinh hs inner join Tab_HocSinhViPham hsvp on hs.MaHocSinh=hsvp.MaHocSinh)";
            sql += "  inner join Tab_Lop l on hs.MaLop=l.MaLop)";
            sql += "  inner join Tab_KyLuat kl on kl.MaKyLuat=hsvp.MaKyLuat";

            SqlCommand command = new SqlCommand(sql);
            m_HocSinhViPhamData.Load(command);
            m_HocSinhViPhamData.CloseConnection();
            return m_HocSinhViPhamData;
        }
        public DataTable layDSHSViPham(string maLop)
        {

            DataService.OpenConnection();

            string sql = "select hs.MaHocSinh, hs.HoTen, l.TenLop, kl.TenKyLuat,kl.MaKyLuat, hsvp.NgayViPham, hs.AnhHocSinh, hsvp.GhiChu";
            sql += " from ((Tab_HocSinh hs inner join Tab_HocSinhViPham hsvp on hs.MaHocSinh=hsvp.MaHocSinh)";
            sql += "  inner join Tab_Lop l on hs.MaLop=l.MaLop)";
            sql += "  inner join Tab_KyLuat kl on kl.MaKyLuat=hsvp.MaKyLuat where hs.MaLop='"+maLop+"'";

            SqlCommand command = new SqlCommand(sql);
            m_HocSinhViPhamData.Load(command);
            m_HocSinhViPhamData.CloseConnection();
            return m_HocSinhViPhamData;
        }
        public DataTable LayHocSinhViPham1(String[] dsvp, String malop)
        {
            DataService.OpenConnection();

                    string sql = "select hs.MaHocSinh, hs.HoTen, l.TenLop, kl.TenKyLuat, CONVERT(varchar(15), hsvp.NgayViPham, 103) AS NgayViPham, hsvp.GhiChu";
            sql += " from ((Tab_HocSinh hs inner join Tab_HocSinhViPham hsvp on hs.MaHocSinh=hsvp.MaHocSinh)";
            sql += "  inner join Tab_Lop l on hs.MaLop=l.MaLop)";
            sql += "  inner join Tab_KyLuat kl on kl.MaKyLuat=hsvp.MaKyLuat";
            sql += " where hsvp.MaKyLuat IN (";
            for (int i = 0; i < dsvp.Length; i++)
            {
                sql += "'"+dsvp[i]+"'";
                if (i < dsvp.Length - 1)
                    sql += ",";
            };
            sql += ") And hs.MaLop='"+malop+"' ";
           SqlCommand command = new SqlCommand(sql);
           //command.Parameters.Add("Ma_Lop", SqlDbType.Char).Value = malop;
            m_HocSinhViPhamData.Load(command);
            m_HocSinhViPhamData.CloseConnection();
            return m_HocSinhViPhamData;
        }
        public HocSinhViPhamInfo layHocSinhViPham(string maKyLuat,string maHocSinh)
        {
            DataService.OpenConnection();
            HocSinhViPhamInfo hs = null;
            string sql = string.Format("select * from Tab_HocSinhViPham where MaKyLuat='{0}' and MaHocSinh='{1}'", maKyLuat,maHocSinh);
            SqlCommand command = new SqlCommand(sql);
            m_HocSinhViPhamData.Load(command);
            if (m_HocSinhViPhamData.Rows.Count == 0)
            {
                m_HocSinhViPhamData.CloseConnection();
                return hs;
            }

            hs = new HocSinhViPhamInfo();
            DataRow dr = m_HocSinhViPhamData.Rows[0];
            hs.MaKyLuat = dr["MaKyLuat"].ToString();
            hs.MaHocSinh = dr["MaHocSinh"].ToString();
            hs.NgayViPham = dr["NgayViPham"].ToString();
            hs.GhiChu = dr["GhiChu"].ToString();
            m_HocSinhViPhamData.CloseConnection();
            return hs;
        }
        public bool themHSViPham(HocSinhViPhamInfo hs)
        {
            DataService.OpenConnection();
            string sql = "Insert into Tab_HocSinhViPham(MaKyLuat,MaHocSinh,NgayViPham,GhiChu)values(@MaKyLuat,@MaHocSinh,@NgayViPham,@GhiChu)";
            SqlCommand command = new SqlCommand(sql);
            command.Parameters.Add("MaKyLuat", SqlDbType.Char).Value = hs.MaKyLuat;
            command.Parameters.Add("MaHocSinh", SqlDbType.Char).Value = hs.MaHocSinh;
            command.Parameters.Add("NgayViPham", SqlDbType.DateTime).Value = DateTime.Parse(hs.NgayViPham);
            command.Parameters.Add("GhiChu", SqlDbType.NVarChar).Value = hs.GhiChu;
            try
            {
                m_HocSinhViPhamData.ExecuteNoneQuery(command);
                m_HocSinhViPhamData.CloseConnection();
                return true;
            }
            catch (Exception ex)
            {
                m_HocSinhViPhamData.CloseConnection();
                Console.WriteLine(ex.Message);
                return false;
            }
          
        }

      

        public bool xoaHocSinhViPham(string maKyLuat,string maHocSinh)
        {
            DataService.OpenConnection();
            string sql = "delete from Tab_HocSinhViPham where MaKyLuat=@MaKyLuat and MaHocSinh=@MaHocSinh";
            SqlCommand command = new SqlCommand(sql);
            command.Parameters.Add("MaKyLuat", SqlDbType.Char).Value = maKyLuat;
            command.Parameters.Add("MaHocSinh", SqlDbType.Char).Value = maHocSinh;
            try
            {
                m_HocSinhViPhamData.ExecuteNoneQuery(command);
                m_HocSinhViPhamData.CloseConnection();
                return true;
            }
            catch (Exception ex)
            {
                m_HocSinhViPhamData.CloseConnection();
                Console.WriteLine(ex.Message);
                return false;
            }
           
        }

        public bool updateHocSinhViPham(HocSinhViPhamInfo hs)
        {
            DataService.OpenConnection();
            string sql = "update Tab_HocSinhViPham set NgayViPham=@NgayViPham,GhiChu=@GhiChu where MaKyLuat=@MaKyLuat and  MaHocSinh=@MaHocSinh";
            SqlCommand command = new SqlCommand(sql);
            command.Parameters.Add("MaHocSinh", SqlDbType.Char).Value = hs.MaHocSinh;
            command.Parameters.Add("NgayViPham", SqlDbType.DateTime).Value = DateTime.Parse(hs.NgayViPham);
            command.Parameters.Add("GhiChu", SqlDbType.NVarChar).Value = hs.GhiChu;
            command.Parameters.Add("MaKyLuat", SqlDbType.Char).Value = hs.MaKyLuat;
            try
            {
                m_HocSinhViPhamData.ExecuteNoneQuery(command);
                m_HocSinhViPhamData.CloseConnection();
                return true;
            }
            catch (Exception ex)
            {
                m_HocSinhViPhamData.CloseConnection();
                Console.WriteLine(ex.Message);
                return false;
            }
          
        }
        
    }
}
